import pymysql
import sqlparse
from config.config import *


def get_connection():
    connection = pymysql.connect(
        host=DB_HOST,
        port=DB_PORT,
        user=DB_USER,
        password=DB_PASSWORD,
        database=DB_NAME,
        charset='utf8mb4',
        autocommit=True
    )
    return connection


def execute_sql_file(filepath, procedure_name):

    connection = get_connection()

    # 读取SQL文件
    with open(filepath, 'r', encoding='utf-8') as f:
        sql = f.read()
        # 使用sqlparse拆分SQL语句(智能识别BEGIN...END...)
        statements = sqlparse.split(sql)

        with connection.cursor() as cursor:

            # 遍历所有智能识别的结果
            for statement in statements:

                # 执行报错 pymysql.err.OperationalError: (1304, 'PROCEDURE TEST already exists')
                # 解决方法
                cursor.execute(statement)

            # 调用存储过程
            cursor.callproc(procedure_name)

            # 打印调试结果
            # result = cursor.fetchall()
            # for row in result:
            #     print(row)

    connection.close()


# 直接运行这个文件时, 先清理测试数据, 再初始化数据, 方便调试测试用例的执行
if __name__ == '__main__':
    execute_sql_file('../config/destroy.sql', 'DESTROY')

    execute_sql_file('../config/init_warehouse_area.sql', 'INIT_WAREHOUSE_AREA')
    execute_sql_file('../config/init_item_type.sql', 'INIT_ITEM_TYPE')
